import json

from app import mysql,help

class Mysql():
    def __init__(self, host, port, user, password, database):
        log = help.Log()
        self.mysql = mysql.MYSQL(host, port, user, password, database, log)

def connect_sql():
    # host = "192.168.0.102"
    host = "120.79.96.200"
    # host = "127.0.0.1"
    port = 3306
    user = "root"
    password = "Aa12345678"
    #database = "jy_incorrupt_government"
    database ="zhikuxuetan"
    get_sql = Mysql(host, port, user, password, database).mysql
    return get_sql

def connect_sql1():
    # host = "192.168.0.102"
    host = "127.0.0.1"
    port = 3306
    user = "root"
    password = "123456"
    #database = "jy_incorrupt_government"
    database ="cell_three_package"
    get_sql = Mysql(host, port, user, password, database).mysql
    return get_sql.conn,get_sql.conn.cursor()





#注射最近注射的针剂，根据名字
def select_all(table,order_data=None):
    get_sql = connect_sql()
    if table !="":
        sql = '''select * from {table}  '''.format(table=table)
    else:
        sql = '''select * from camera_info '''

    if order_data!=None:
        sql = sql + " order by %s"%order_data
    result = get_sql.select(sql)
    return result

#注射最近注射的针剂，根据名字
def select_with_sql(sql,order_data=None):
    get_sql = connect_sql()
    result = get_sql.select(sql)
    return result

def insert_data_list_many(sql_data,temp_table):
    conn,cur = connect_sql1()
    columns = ','.join(list(sql_data.columns))

    # 构造每个列对应的数据，对应于上面的((value1, value2, value3))
    data_list = [tuple(i) for i in sql_data.values]  # 每个元组都是一条数据，根据df行数生成多少元组数据

    # 计算一行有多少value值需要用字符串占位
    s_count = len(data_list[0]) * "%s,"

    # 构造sql语句
    insert_sql = "insert into " + temp_table + " (" + columns + ") values (" + s_count[:-1] + ")"

    try:
        cur.executemany(insert_sql, data_list)
        conn.commit()
        cur.close()
        conn.close()
    except Exception as e:
        # 万一失败了，要进行回滚操作
        conn.rollback()
        cur.close()
        conn.close()

    #插入数据列表
def select_data_list(sql_data,table,order_data=None):
    result=None
    if type(sql_data) ==dict and sql_data!={}:
        try:
            select_value =""
            for key in sql_data.keys():
                if type(sql_data[key]) != int :
                    select_value = select_value + " and %s = '%s'" % (key, sql_data[key])

                    # value_data = value_data + "'%s'" % sql_data[key] + ","
                else:
                    select_value = select_value + " and %s = %s" % (key, sql_data[key])
            if select_value!="":
                select_value =  "where"+select_value
                select_value = select_value.replace("where and","where ")
                sql = "select * from {table} {select_value}".format(select_value=select_value,table=table)
            else:
                sql = "select * from {table} ".format(table=table)

            if order_data!=None:
                sql = sql + " order by %s"%str(order_data)
            sql = sql.replace(",)",")")
            get_sql = connect_sql()
            result = get_sql.select(sql)
            # if result!=None:
            #     result = True
        except Exception as e:
            print(e)

    #为空时，选择全部
    elif sql_data=={}:
        get_sql = connect_sql()
        if table != "":
            sql = '''select * from {table}  '''.format(table=table)
            if order_data != None:
                sql = sql + " order by %s" % order_data
            result = get_sql.select(sql)

    return result


#插入数据列表
def insert_data_list(sql_data,table):
    result=None
    if type(sql_data) ==dict:
        try:
            name_data = ""
            value_data = ""
            for key in sql_data.keys():
                name_data = name_data + key + ","

                if type(sql_data[key]) != int:
                    if type(sql_data[key]) ==dict or type(sql_data[key]) ==list:
                        value_data = value_data + "'%s'" % json.dumps(sql_data[key]) + ","
                    else:
                        value_data = value_data + "'%s'" % sql_data[key] + ","
                else:
                    value_data = value_data + str(sql_data[key]) + ","
            sql = '''insert into {table} ({name_data}) values ({value_data})'''.format(name_data=name_data,
                                                                         value_data=value_data,table=table)
            sql = sql.replace(",)",")")
            get_sql = connect_sql()
            result = get_sql.insert(sql)
            # if result!=None:
            #     result = True
        except Exception as e:
            print(e)

    return result


#插入数据列表
def insert_ignore_list(sql_data,table):
    result=None
    if type(sql_data) ==dict:
        try:
            name_data = ""
            value_data = ""
            for key in sql_data.keys():
                name_data = name_data + key + ","

                if type(sql_data[key]) != int:
                    if type(sql_data[key]) ==dict or type(sql_data[key]) ==list:
                        value_data = value_data + "'%s'" % json.dumps(sql_data[key]) + ","
                    else:
                        value_data = value_data + "'%s'" % sql_data[key] + ","
                else:
                    value_data = value_data + str(sql_data[key]) + ","
            sql = '''insert ignore into {table} ({name_data}) values ({value_data})'''.format(name_data=name_data,
                                                                         value_data=value_data,table=table)
            sql = sql.replace(",)",")")
            get_sql = connect_sql()
            result = get_sql.insert(sql)
            # if result!=None:
            #     result = True
        except Exception as e:
            print(e)

    return result


#插入数据列表
def update_data_list(sql_data,table):
    result=None
    if type(sql_data) ==dict:
        try:
            update_data=""
            k0=0
            for key in sql_data.keys():

                if type(sql_data[key]) !=int:
                    update_data = update_data+"{get_key}='{get_value}',".format(get_key = key,get_value =sql_data[key])
                else:
                    update_data = update_data + "{get_key}={get_value},".format(get_key=key, get_value=sql_data[key])

            sql = "update {table} set {update_data},,".format(table=table,update_data=update_data)
            sql = sql.replace(",,,","")
            sql = sql +" where ip ='{ip}'".format(ip=sql_data["ip"])
            get_sql = connect_sql()
            result = get_sql.update(sql)
            if result!=None:
                result = True
        except Exception as e:
            print(e)
    return result

#删除摄像头
def delete_sql(sql_data,table):
    result = None
    if type(sql_data) == dict:
        try:
            delete_data = ""
            k0 = 0
            for key in sql_data.keys():

                if type(sql_data[key]) != int:
                    delete_data = delete_data + "{get_key}='{get_value}' and ".format(get_key=key, get_value=sql_data[key])
                else:
                    delete_data = delete_data + "{get_key}={get_value} and ".format(get_key=key, get_value=sql_data[key])

            sql = "delete from {table} where {delete_data},,".format(table=table, delete_data=delete_data)
            sql = sql.replace("and ,,", "")
            sql = sql +" order by id limit 10"
            get_sql = connect_sql()
            result = get_sql.commit(sql)
            if result != None:
                result = True
        except Exception as e:
            print(e)
    return result


#插入数据列表
def update_target_list(table,update_sql,search_sql=None):
    result=None
    if type(update_sql) ==dict:
        try:
            update_data=""
            k0=0
            for key in update_sql.keys():
                if type(update_sql[key]) !=int:
                    update_data = update_data+"{get_key}='{get_value}',".format(get_key = key,get_value =update_sql[key])
                else:
                    update_data = update_data + "{get_key}={get_value},".format(get_key=key, get_value=update_sql[key])

            sql = "update {table} set {update_data},,".format(table=table,update_data=update_data)

            sql = sql.replace(",,,","")

            if search_sql !=None and search_sql!={}:
                search_data = ""
                for key in search_sql.keys():
                    if type(search_sql[key]) != int:
                        search_data = search_data + "{get_key}='{get_value}' and ".format(get_key=key,
                                                                                      get_value=search_sql[key])
                    else:
                        search_data = search_data + "{get_key}={get_value} and ".format(get_key=key,
                                                                                    get_value=search_sql[key])
                search_data = search_data +",,"
                search_data = search_data.replace("and ,,", "")

                sql = sql +" where " +search_data
            get_sql = connect_sql()
            result = get_sql.update(sql)
            if result!=None and result!=0:
                result = True
        except Exception as e:
            print(e)
    return result


#sf,note检查。
def note_check_status(sql_data,table):
    result = None
    if type(sql_data) == dict:
        try:
            update_data = ""
            k0 = 0
            for key in sql_data.keys():
                if type(sql_data[key]) != int:
                    update_data = update_data + "{get_key}='{get_value}',".format(get_key=key, get_value=sql_data[key])
                else:
                    update_data = update_data + "{get_key}={get_value},".format(get_key=key, get_value=sql_data[key])
            sql = "update {table} set {update_data},,".format(table=table, update_data=update_data)
            sql = sql.replace(",,,", "")
            sql = sql + " where ip ='{ip}'".format(ip=sql_data["ip"])
            get_sql = connect_sql()
            result = get_sql.update(sql)
            if result != None:
                result = True
        except Exception as e:
            print(e)
    return result


#根据不同的参数区分选择
def select_distinct_list(sql_list,table,order_data=None):
    result = None
    sql="select distinct "
    if type(sql_list) == list and sql_list != []:
        for one_data in sql_list:
            sql =sql + " "+str(one_data)

    sql = sql +" from %s"%table
    if order_data !=None:
        sql = sql +" order by %s"%(order_data)

    get_sql = connect_sql()
    result = get_sql.select(sql)
    return result

#选择不同的mode
def select_distinct_mode(table):
    try:
        sql = "select distinct turn_number,method_number,method_table,out_key,data_order,mode_id from %s where out_key is not NULL;" % (
            table)
        get_sql = connect_sql()
        result = get_sql.select(sql)
        return result
    except Exception as e:
        pass

    return ()



#插入数据列表
def insert_multy_list(sql_list,table):
    result=None
    k=0
    sql=""
    for sql_data in sql_list:
        if k==0 and type(sql_data) == dict:
            try:
                name_data = ""
                value_data = ""
                for key in sql_data.keys():
                    name_data = name_data + key + ","
                    if type(sql_data[key]) != int:
                        if type(sql_data[key]) == dict or type(sql_data[key]) == list:
                            value_data = value_data + "'%s'" % json.dumps(sql_data[key]) + ","
                        else:
                            value_data = value_data + "'%s'" % sql_data[key] + ","
                    else:
                        value_data = value_data + str(sql_data[key]) + ","
                sql = '''insert into {table} ({name_data}) values ({value_data})'''.format(name_data=name_data,
                                                                                           value_data=value_data,
                                                                                           table=table)
                sql = sql.replace(",)", ")")
                # get_sql = connect_sql()
                # result = get_sql.insert(sql)

            except Exception as e:
                print(e)
        elif k!=0 and type(sql_data) == dict:
            try:
                name_data = ""
                value_data = ""
                for key in sql_data.keys():
                    name_data = name_data + key + ","
                    if type(sql_data[key]) != int:
                        if type(sql_data[key]) == dict or type(sql_data[key]) == list:
                            value_data = value_data + "'%s'" % json.dumps(sql_data[key]) + ","
                        else:
                            value_data = value_data + "'%s'" % sql_data[key] + ","
                    else:
                        value_data = value_data + str(sql_data[key]) + ","
                sql = sql+ ''',({value_data})'''.format(value_data=value_data)
                sql = sql.replace(",)", ")")

                # if result!=None:
                #     result = True
            except Exception as e:
                print(e)
        k=k+1
    try:
        get_sql = connect_sql()
        result = get_sql.insert(sql)
    except Exception as e:
        pass
    return result























